﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// Summary description for datacessDB
/// </summary>
public class datacessDB
{
	public datacessDB()
	{
	}
    private string conn = ConfigurationManager.ConnectionStrings["eproject_sem3"].ConnectionString.ToString();
    public List<Customer> getAllCustomer()
    {
        List<Customer> lstCustomer = new List<Customer>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from customer";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Customer c = new Customer();
            c.CustomerId = reader.GetInt32(0);
            c.Username = reader.GetString(1);
            c.Password = reader.GetString(2);
            c.Name = reader.GetString(3);
            c.Birthday = reader.GetDateTime(4);
            c.Gender = reader.GetString(5);
            c.Email = reader.GetString(6);
            c.Status = reader.GetString(7);
            lstCustomer.Add(c);
        }
        con.Close();
        return lstCustomer;
    }
    public List<Ecard> getAllEcard()
    {
        List<Ecard> lstEcard = new List<Ecard>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from Ecard";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Ecard e = new Ecard();
            e.EcardId = reader.GetInt32(0);
            e.UrlImage = reader.GetString(1);
            e.UrlFlash = reader.GetString(2);
            e.EcardName = reader.GetString(3);
            e.AddDate = reader.GetDateTime(4);
            e.TypeId = reader.GetInt32(5);
            e.Count = reader.GetInt32(6);
            lstEcard.Add(e);
        }
        con.Close();
        return lstEcard;
    }
    public List<SentEcard> getAllSentEcard()
    {
        List<SentEcard> lstSentEcard = new List<SentEcard>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from SentEcard";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            SentEcard se = new SentEcard();
            se.SentEcardId = reader.GetInt32(0);
            se.CustomerId = reader.GetInt32(1);
            se.EcardId = reader.GetInt32(2);
            se.DateSent = reader.GetDateTime(3);
            se.Title = reader.GetString(4);
            se.Massage = reader.GetString(5);
            se.Signature = reader.GetString(6);
            lstSentEcard.Add(se);
        }
        con.Close();
        return lstSentEcard;
    }
    public List<listEmail> getAllListEmail()
    {
        List<listEmail> lstListEmail = new List<listEmail>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from listEmail";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            listEmail email = new listEmail();
            email.SentEcardId = reader.GetInt32(0);
            email.Email = reader.GetString(1);
            email.Name = reader.GetString(2);
            lstListEmail.Add(email);
        }
        con.Close();
        return lstListEmail;
    }
    public void insertcustomer(Customer custom )
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into Customer values ('"+custom.Username+"','"+custom.Password+"','"+custom.Name+"','"+custom.Birthday+"','"+custom.Gender+"','"+custom.Email+"','normal')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertSentEcard(SentEcard se)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into SentEcard values (" + se.CustomerId + "," + se.EcardId + ",'" + se.DateSent + "','" + se.Title + "','" +se.Massage + "','" + se.Signature + "')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertListEmail(listEmail le)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into listEmail values (" + le.SentEcardId+ ",'" + le.Email+ "','" + le.Name + "')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public int getSentEcardId(int customerId)
    {
        int id=0;
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 1 sentEcardId from sentEcard where customerId ="+customerId+" order by sentEcardId desc ";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        while(reader.Read())
        {
            id = reader.GetInt32(0);
        }
        con.Close();
        return id;
        
    }
    public Customer getCustomerById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from customer where customerId ="+id+"";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        Customer c = new Customer();
        while(reader.Read())
        {
            c.CustomerId = reader.GetInt32(0);
            c.Username = reader.GetString(1);
            c.Password = reader.GetString(2);
            c.Name = reader.GetString(3);
            c.Birthday = reader.GetDateTime(4);
            c.Gender = reader.GetString(5);
            c.Email = reader.GetString(6);
            c.Status = reader.GetString(7);
        }
       
        con.Close();
        return c;
    }
    public SentEcard getSentEcardById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from SentEcard where sentEcardId =" + id + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        SentEcard se = new SentEcard();
        while (reader.Read())
        {
            
            se.SentEcardId = reader.GetInt32(0);
            se.CustomerId = reader.GetInt32(1);
            se.EcardId = reader.GetInt32(2);
            se.DateSent = reader.GetDateTime(3);
            se.Title = reader.GetString(4);
            se.Massage = reader.GetString(5);
            se.Signature = reader.GetString(6);
        }

        con.Close();
        return se;
    }
}